﻿#nullable enable
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.EntityFrameworkCore;

namespace _069.EFCore
{
	public class Program
	{
		static async Task Main(string[] args)
		{
			// TestDbContext 逻辑上的数据库
			//using (TestDbContext ctx = new TestDbContext())
			//{
			//	// 数据的插入
			//	var dog = new Dog();
			//	dog.Name = "aaa";
			//	// 把dog对象加入Dogs这个逻辑上的表里面
			//	ctx.Dogs.Add(dog);


			//	var dog2 = new Dog();
			//	dog2.Name = "bbb";
			//	// 把dog对象加入Dogs这个逻辑上的表里面
			//	ctx.Dogs.Add(dog2);

			//	var book = new Book();
			//	book.AuthorName = "fzn";
			//	book.Price = 100;
			//	book.Title = "java";
			//	book.PubTime = DateTime.Now;

			//	ctx.Books.Add(book);

			//	// 保存修改 Update-Database
			//	//ctx.SaveChanges(); 有异步方法
			//	await ctx.SaveChangesAsync();
			//}

			/*using TestDbContext ctx = new TestDbContext();
			var b1 = new Book
			{
				AuthorName = "杨中科",
				Title = "零基础趣学C语言",
				Price = 59.8,
				PubTime = new DateTime(2019, 3, 1)
			};
			var b2 = new Book
			{
				AuthorName = "Robert Sedgewick",
				Title = "算法(第4版)",
				Price = 99,
				PubTime = new DateTime(2012, 10, 1)
			};
			var b3 = new Book
			{
				AuthorName = "吴军",
				Title = "数学之美",
				Price = 69,
				PubTime = new DateTime(2020, 5, 1)
			};
			var b4 = new Book
			{
				AuthorName = "杨中科",
				Title = "程序员的SQL金典",
				Price = 52,
				PubTime = new DateTime(2008, 9, 1)
			};
			var b5 = new Book
			{
				AuthorName = "吴军",
				Title = "文明之光",
				Price = 246,
				PubTime = new DateTime(2017, 3, 1)
			};
			ctx.Books.Add(b1);
			ctx.Books.Add(b2);
			ctx.Books.Add(b3);
			ctx.Books.Add(b4);
			ctx.Books.Add(b5);
			await ctx.SaveChangesAsync();*/

			// 查询
			/*using TestDbContext ctx = new TestDbContext();
			Console.WriteLine("***所有书籍***");
			foreach (Book b in ctx.Books)
			{
				Console.WriteLine($"Id={b.Id},Title={b.Title},Price={b.Price}");
			}
			Console.WriteLine("***所有价格高于80元的书籍***");
			IEnumerable<Book> books2 = ctx.Books.Where(b => b.Price > 80);
			foreach (Book b in books2)
			{
				Console.WriteLine($"Id={b.Id},Title={b.Title},Price={b.Price}");
			}*/

			//Single、FirstOrDefault查询
			/*using TestDbContext ctx = new TestDbContext();
			Book b1 = ctx.Books.Single(b => b.Title == "零基础趣学C语言");
			Console.WriteLine($"Id={b1.Id},Title={b1.Title},Price={b1.Price}");
			Book? b2 = ctx.Books.FirstOrDefault(b => b.Id == 9);
			if (b2 == null)
			{
				Console.WriteLine("没有Id=9的数据");
			}
			else
			{
				Console.WriteLine($"Id={b2.Id},Title={b2.Title},Price={b2.Price}");
			}*/

			//排序

			/*using TestDbContext ctx = new TestDbContext();
			IEnumerable<Book> books = ctx.Books.OrderByDescending(b => b.Price);
			foreach (Book b in books)
			{
				Console.WriteLine($"Id={b.Id},Title={b.Title},Price={b.Price}");
			}*/

			// 分组查询
			using TestDbContext ctx = new TestDbContext();
			
			var groups = ctx.Books.GroupBy(b => b.AuthorName)
				.Select(g => new { AuthorName = g.Key, BooksCount = g.Count(), MaxPrice = g.Max(b => b.Price) });
			foreach (var g in groups)
			{
				Console.WriteLine($"作者名:{g.AuthorName},著作数量:{g.BooksCount},最贵的价格:{g.MaxPrice}");
			}

			// 方法三 打印查询字符串
			Console.WriteLine(groups.ToQueryString());

			// 修改  修改要先查询出来 再修改
			/*var b = ctx.Books.Single(b => b.Title == "数学之美");
			b.AuthorName = "Jun Wu";*/

			// 删除 也是先查出来 再去删除 
			//var b = ctx.Books.Single(b => b.Title == "数学之美");
			//ctx.Remove(b);//也可以写成ctx.Books.Remove(b);


			// 保存更改  查询不需要
			await ctx.SaveChangesAsync();
		}
	}
}
